﻿using Dapper;
using StudentWeb.Common;
using StudentWeb.IDAL;
using StudentWeb.Model;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace StudentWeb.DAL
{
	/// <summary>
	/// TeacherData的数据操作类
	/// </summary>
    [Service(EServLife.Singleton)]
	public partial class TeacherDataDAO(IConnectionFactory connFactory) : ITeacherDataDAO
	{
        #region 添加单条记录
        /// <summary>
		/// 添加单条记录
		/// </summary>
		/// <param name="teacherData">实体</param>
        /// <param name="trans">事务</param>
		/// <returns>影响行数</returns>
        public int Insert(TeacherData teacherData, IDbTransaction? trans = null)
        {
            var sql = "INSERT INTO TeacherData(Mobile, Name, Password) VALUES (@Mobile, @Name, @Password);SELECT CAST(SCOPE_IDENTITY() AS int)";

            if (trans != null && trans.Connection != null)
            {
                var teacherID = trans.Connection.Query<int>(sql, teacherData, trans).Single();
                teacherData.TeacherID = teacherID;
                var result = 1;
                return result;
            }
            using (var conn = connFactory.GetConnection())
            {
                conn.Open();
                var teacherID = conn.Query<int>(sql, teacherData).Single();
                teacherData.TeacherID = teacherID;
                var result = 1;
                conn.Close();
                return result;
            }
        }
        #endregion
        #region 添加多条记录
        /// <summary>
		/// 添加多条记录
		/// </summary>
		/// <param name="list">列表</param>
        /// <param name="trans">事务</param>
		/// <returns>影响行数</returns>
        public int Insert(List<TeacherData> list, IDbTransaction? trans = null)
        {
            var sql = "INSERT INTO TeacherData(Mobile, Name, Password) VALUES (@Mobile, @Name, @Password)";
            var result = 0;
            if (trans != null && trans.Connection != null)
            {
                result = trans.Connection.Execute(sql, list, trans);
                return result;
            }
            using (var conn = connFactory.GetConnection())
            {
                conn.Open();
                result = conn.Execute(sql, list);
                conn.Close();
                return result;
            }
        }
        #endregion
        #region 编辑单条记录
        /// <summary>
		/// 编辑单条记录
		/// </summary>
		/// <param name="teacherData">实体</param>
        /// <param name="fields">要更新的列</param>
        /// <param name="trans">事务</param>
		/// <returns>影响行数</returns>
        public int Update(TeacherData teacherData, string? fields = null, IDbTransaction? trans = null)
        {
            if (string.IsNullOrEmpty(fields))
                fields = "Mobile = @Mobile, Name = @Name, Password = @Password";
            else
                fields = DalHelper.FieldsToUpdates(fields);
            var sql = $"UPDATE TeacherData SET {fields} WHERE TeacherID = @TeacherID";
            var result = 0;
            if (trans != null && trans.Connection != null)
            {
                result = trans.Connection.Execute(sql, teacherData, trans);
                return result;
            }
            using (var conn = connFactory.GetConnection())
            {
                conn.Open();
                result = conn.Execute(sql, teacherData);
                conn.Close();
                return result;
            }
        }
        #endregion
        #region 编辑多条记录
        /// <summary>
		/// 编辑多条记录
		/// </summary>
		/// <param name="list">实体列表</param>
        /// <param name="fields">要更新的列</param>
        /// <param name="trans">事务</param>
		/// <returns>影响行数</returns>
        public int Update(List<TeacherData> list, string? fields = null, IDbTransaction? trans = null)
        {
            if (string.IsNullOrEmpty(fields))
                fields = "Mobile = @Mobile, Name = @Name, Password = @Password";
            else
                fields = DalHelper.FieldsToUpdates(fields);
            var sql = $"UPDATE TeacherData SET {fields} WHERE TeacherID = @TeacherID";
            var result = 0;
            if (trans != null && trans.Connection != null)
            {
                result = trans.Connection.Execute(sql, list, trans);
                return result;
            }
            using (var conn = connFactory.GetConnection())
            {
                conn.Open();
                result = conn.Execute(sql, list);
                conn.Close();
                return result;
            }
        }
        #endregion
        #region 删除单条记录
        /// <summary>
		/// 删除单条记录
		/// </summary>
		/// <param name="teacherID">主键</param>
        /// <param name="trans">事务</param>
		/// <returns>影响行数</returns>
        public int Delete(int teacherID, IDbTransaction? trans = null)
        {
            var sql = "DELETE TeacherData WHERE TeacherID = @TeacherID";
            var result = 0;
            if (trans != null && trans.Connection != null)
            {
                result = trans.Connection.Execute(sql, new { TeacherID = teacherID }, trans);
                return result;
            }
            using (var conn = connFactory.GetConnection())
            {
                conn.Open();
                result = conn.Execute(sql, new { TeacherID = teacherID });
                conn.Close();
                return result;
            }
        }
        #endregion
        #region 删除单条记录
        /// <summary>
		/// 删除单条记录
		/// </summary>
		/// <param name="teacherData">实体</param>
        /// <param name="trans">事务</param>
		/// <returns>影响行数</returns>
        public int Delete(TeacherData teacherData, IDbTransaction? trans = null)
        {
            var sql = "DELETE TeacherData WHERE TeacherID = @TeacherID";
            var result = 0;
            if (trans != null && trans.Connection != null)
            {
                result = trans.Connection.Execute(sql, teacherData, trans);
                return result;
            }
            using (var conn = connFactory.GetConnection())
            {
                conn.Open();
                result = conn.Execute(sql, teacherData);
                conn.Close();
                return result;
            }
        }
        #endregion
        #region 获取单条记录
        /// <summary>
		/// 获取单条记录
		/// </summary>
		/// <param name="teacherID">主键</param>
        /// <param name="fields">要获取的列</param>
		/// <returns>实体</returns>
        public T? GetInfo<T>(int teacherID, string? fields = null)
        {
            if (string.IsNullOrEmpty(fields)) { fields = "*"; }
            var sql = $"SELECT {fields} FROM TeacherData WHERE TeacherID = @TeacherID";
            using (var conn = connFactory.GetConnection())
            {
                var model = conn.QueryFirstOrDefault<T>(sql, new { TeacherID = teacherID });
                return model;
            }
        }
        public TeacherData? GetInfo(int teacherID) { return GetInfo<TeacherData>(teacherID); }
        public T? GetInfo<T>(string? whereClause = null, object? param = null, string? fields = null)
        {
            if (string.IsNullOrEmpty(fields)) { fields = "*"; }
            if (!string.IsNullOrEmpty(whereClause))
            {
                whereClause = "WHERE " + whereClause;
            }
            var sql = $"SELECT {fields} FROM TeacherData {whereClause}";
            using (var conn = connFactory.GetConnection())
            {
                var model = conn.QueryFirstOrDefault<T>(sql, param);
                return model;
            }
        }
        public TeacherData? GetInfo(string? whereClause = null, object? param = null)
        {
            return GetInfo<TeacherData>(whereClause, param);
        }
        #endregion
        #region 获取全部记录列表
        /// <summary>
		/// 获取全部记录列表
		/// </summary>
        /// <param name="fields">要获取的列</param>
        /// <param name="whereClause">条件语句</param>
        /// <param name="orderBy">排序规则</param>
		/// <returns>实体列表</returns>
        public List<T> GetList<T>(string? whereClause = null, object? param = null, string? orderBy = null, string? fields = null)
        {
            if (string.IsNullOrEmpty(fields)) { fields = "*"; }
            if (!string.IsNullOrEmpty(whereClause))
            {
                whereClause = " WHERE " + whereClause;
            }
            if (!string.IsNullOrEmpty(orderBy))
            {
                orderBy = " ORDER BY " + orderBy;
            }
            var sql = $"SELECT {fields} FROM TeacherData {whereClause} {orderBy}";
            using (var conn = connFactory.GetConnection())
            {
                var q = conn.Query<T>(sql, param);
                return q.ToList();
            }
        }
        public List<TeacherData> GetList(string? whereClause = null, object? param = null, string? orderBy = null)
        {
            return GetList<TeacherData>(whereClause, param, orderBy);
        }
        #endregion
        #region 分页列表
        /// <summary>
        /// 分页列表
        /// </summary>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">页面大小</param>
        /// <param name="fields">要获取的列</param>
        /// <param name="orderBy">排序规则</param>
        /// <param name="whereClause">条件语句</param>
        /// <returns>分页列表</returns>
        public PagedList<T> GetList<T>(int pageIndex, int pageSize, string? whereClause = null, object? param = null, string? orderBy = null, string? fields = null)
        {
            pageIndex--;
            if (string.IsNullOrEmpty(fields)) { fields = "*"; }
            if (!string.IsNullOrEmpty(whereClause))
            {
                whereClause = " WHERE " + whereClause;
            }
            if (string.IsNullOrEmpty(orderBy))
            {
                orderBy = "TeacherID DESC";
            }
            var startItem = pageIndex * pageSize + 1;
            var endItem = (pageIndex + 1) * pageSize;

            var sql = 
$@"SELECT COUNT(TeacherID) FROM TeacherData {whereClause} 
SELECT *
FROM(SELECT ROW_NUMBER() OVER(ORDER BY {orderBy}) AS RowNum, {fields}
          FROM TeacherData
          {whereClause}
        ) AS result
WHERE RowNum >= {startItem}
    AND RowNum <= {endItem}
ORDER BY RowNum";
            using (var conn = connFactory.GetConnection())
            {
                conn.Open();
                int count = 0;
                var list = new List<T>();
                using (var q = conn.QueryMultiple(sql, param))
                {
                    count = q.Read<int>().Single();
                    list = q.Read<T>().ToList();
                }
                conn.Close();

                var pagedList = new PagedList<T>(list, pageIndex, pageSize, count);
                return pagedList;
            }
        }
        public PagedList<TeacherData> GetList(int pageIndex, int pageSize, string? whereClause = null, object? param = null, string? orderBy = null)
        {
            return GetList<TeacherData>(pageIndex, pageSize, whereClause, param, orderBy);
        }
        #endregion
	}
}

